package com.aiflow.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.aiflow.bean.FrontUser;
import com.aiflow.bean.User;

public class ConnDB {
    private final String driver1="com.microsoft.sqlserver.jdbc.SQLServerDriver";
    static Connection conn = null;
    static Statement stmt = null;
    static ResultSet rs = null;
    public ConnDB(){
    }

    //数据库连接
   public Connection getConnection()
   {
       Connection conn=null;
       try
       {
           Class.forName(driver1);
           conn=DriverManager.getConnection("proxool.sqlserver");
           //conn.setAutoCommit(false);
           return conn;
       }
       catch(ClassNotFoundException ex)
       {
           ex.printStackTrace();
           return null;
       }
       catch(SQLException ex1)
       {
           ex1.printStackTrace();
           return null;
       }
   }


    public void closeConn()
    {
        try
        {
            if(conn!=null)
              conn.close();
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
    }

    public void closeStmt()
    {
      try
      {
          if(stmt!=null)
            stmt.close();
      }
      catch(SQLException ex)
      {
          ex.printStackTrace();
      }
    }

    public void closeRs(ResultSet rs)
    {
        try
        {
            if(rs!=null)
              rs.close();
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
    }

    public ResultSet returnRs(String str)
    {
        try
        {
            conn = this.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(str);
            return rs;
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
            return null;
        }
    }
    
  //获取信息记录总数 
	public int GetInfoCount(String sql) { 
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
	    int i = 0;
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				i = Integer.parseInt(rs.getString(1));
			}
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally
		{
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	    return i; 
	}
	
	//获取当前用户的所有角色ID 
	public List<User> GetUserRole(String user_id) { 
		List<User> listids = new ArrayList<User>();
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select id,login_acct,login_pass,name,role_id,dept_id,state,add_time,expired_time from user_t where id = ?";
			ps = con.prepareStatement(sql);
			ps.setString(1,user_id);
			rs = ps.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getString(1));
				user.setLogin_acct(rs.getString(2));
				user.setLogin_pass(rs.getString(3));
				user.setName(rs.getString(4));
				user.setRole_id(rs.getString(5));
				user.setDept_id(rs.getString(6));
				user.setState(rs.getString(7));
				user.setAdd_time(rs.getString(8));
				user.setExpired_time(rs.getString(9));
				listids.add(user);
			}
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally
		{
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	    return listids; 
	}
	
	 //获取当前角色的所有权限ID 
	public List<String> GetRolePer(String role_id) { 
		List<String> listids = new ArrayList<String>();
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select per_id from t_u_per_role where role_id="+role_id;
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				listids.add(rs.getString(1));
			}
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally
		{
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	    return listids; 
	}
	
	//删除用户的所有角色
	public void deleteUserRole(String user_id){
		ConnDB db = new ConnDB();
		Connection con =  db.getConnection();
		String	sql = "delete from t_u_role_user where user_id="+user_id;
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			int rs = ps.executeUpdate();
			if(rs > 0){
				System.out.println("删除成功!!!");
			}
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	//删除角色的所有权限
	public void deleteRolePer(String role_id){
		ConnDB db = new ConnDB();
		Connection con =  db.getConnection();
		String	sql = "delete from t_u_per_role where role_id="+role_id;
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			int rs = ps.executeUpdate();
			if(rs > 0){
				System.out.println("删除成功!!!");
			}
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	//修改用户的角色
	public void updateUserRoleInfor(String user_id,String roleids){
		ConnDB db = new ConnDB();
		//当前用户的所有角色的数组
		String[] role_ids = roleids.split(",");
		Connection con =  db.getConnection();
		String sql = "insert into t_u_role_user(user_id,role_id) values(?,?)";
		PreparedStatement ps = null;
		try {
			//先删除当前用户的所有角色，然后在新增选中的角色
			db.deleteRolePer(user_id);
			ps = con.prepareStatement(sql);
			for (int i = 0; i < role_ids.length; i++) {
				int index = 0;
				ps.setString(++index, user_id);
				ps.setString(++index, role_ids[i]);
				ps.addBatch();
			}
			int[] rs = ps.executeBatch();
			if(rs.length > 0){
				System.out.println("保存成功!!!");
			}
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	 //修改密码时验证原密码是否正确 
	public boolean login(String longinname,String password) { 
		boolean flag = false;
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
		try {
			String sql = "select * from t_u_user_Info where Login_name='"+longinname+"' and password='"+password+"'";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				flag = true;
			}
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally
		{
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	    return flag; 
	}
	
	//获取信息记录总数 
	public int GetDataCount(String sql) { 
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		Connection con =  db.getConnection();
		sql = "select count(1) from ( "+sql+") tab"  ;
		PreparedStatement ps = null;
	    int i = 0;
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				i = Integer.parseInt(rs.getString(1));
			}
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally
		{
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	    return i; 
	}
	
	//验证用户登录 
	public boolean valilogin(String username,String password) { 
		boolean flag = false;
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		Connection con =  db.getConnection();
		String sql = "select * from agent_t where login_acct = ? and login_pass =?"; 
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1,username);
			ps.setString(2,password);
			rs = ps.executeQuery();
			while (rs.next()) {
				flag = true;
			}
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally
		{
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	    return flag; 
	}
	
	//页面登陆获取用户有哪些权限 
	public String getRolePer(int roleid) { 
		String perid = "";
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		Connection con =  db.getConnection();
		String sql = "select * from privilege_t "+
					 "where id in "+ 
					 "(select distinct pid from privilege_t where id in "+
					 "(select distinct privilege_id from role_privilege_t "+ 
					 "where role_id in ("+roleid+")))"; 
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				perid = rs.getString(1)+","+perid;
			}
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally
		{
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	    return perid.trim(); 
	}
	
	//根据登录名返回User对象 
	public FrontUser returnUser(String longinname) { 
		FrontUser user = new FrontUser();
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
		try {
//			String sql = "select id,login_acct,login_pass,name,role_id,dept_id,state,add_time,expired_time from user_t where login_acct = ?";
			String sql = "select id,login_acct,login_pass,name,store_name,address,phone_num,vest_clerk_id,area,state,role_id,busi_pass from agent_t where login_acct = ? and state = 1";
			ps = con.prepareStatement(sql);
			ps.setString(1,longinname);
			rs = ps.executeQuery();
			while (rs.next()) {
				user.setId(rs.getString(1));
				user.setLogin_acct(rs.getString(2));
				user.setLogin_pass(rs.getString(3));
				user.setName(rs.getString(4));
				user.setStore_name(rs.getString(5));
				user.setAddress(rs.getString(6));
				user.setPhone_num(rs.getString(7));
				user.setVest_clerk_id(Integer.parseInt(rs.getString(8)));
				user.setArea(Integer.parseInt(rs.getString(9)));
				user.setState(Integer.parseInt(rs.getString(10)));
				user.setRole_id(Integer.parseInt(rs.getString(11)));
				user.setBusi_pass(rs.getString(12));
			}
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally
		{
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	    return user; 
	}
	
	public void closeConnect(Connection conn){
    	if(conn != null){
    		try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    public void closeStatement(Statement statement){
    	if(statement != null){
    		try {
    			statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    public void closeResultSet(ResultSet resultSet){
    	if(resultSet != null){
    		try {
    			resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    //新增通讯录
	public void insertPhonelist(String phoneid){
		ConnDB db = new ConnDB();
		//当前用户的所有角色的数组
		Connection con =  db.getConnection();
		String sql = "insert into t_s_phonelist(phoneid,phone) values(?,?)";
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			int index = 0;
			ps.setString(++index, phoneid);
			ps.setString(++index, "");
			int rs = ps.executeUpdate();
			if(rs > 0){
		//		System.out.println("保存成功!!!");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	//返回要通知的通讯录组 
	public String getPhones(String ids) { 
		String phone = "";
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		Connection con =  db.getConnection();
		String sql = "select id,phoneid,name,phone from dbo.t_s_phonelist "+ 
					 "where id = ?"; 
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, ids);
			rs = ps.executeQuery();
			while (rs.next()) {
				phone = rs.getString(4);
			}
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally
		{
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	    return phone; 
	}
	
	//验证插入的告警标题是否有重复 
	public boolean valiAlarmpro(String alarmType,String alarmTitle) { 
		boolean flag = false;
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		Connection con =  db.getConnection();
		String sql = "select * from dbo.Alarm_pro where alarm_type = ? and alarm_title =?"; 
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1,alarmType);
			ps.setString(2,alarmTitle);
			rs = ps.executeQuery();
			while (rs.next()) {
				flag = true;
			}
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally
		{
            try
            {
            	ps.close();
            	rs.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	    return flag; 
	}
	
	public static void main(String[] args) {
		ConnDB db = new ConnDB();
//		String tt = db.getRolePer("7,8");
//		System.out.println(tt);
	}
}
